package cn.edu.ctbu.myfirstweb.dao;

import cn.edu.ctbu.myfirstweb.domain.Course;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.io.Serializable;
import java.util.List;

public interface CourseRepository extends JpaRepository<Course, Long>,
        JpaSpecificationExecutor<CourseRepository>,
        Serializable {

    @Query(nativeQuery = true, value = "select * from tb_course where cid in(select cid from tb_tc where tid = :tid)")
    List<Course> getCourseByTid(@Param("tid") Long tid);

    Course getCourseByCid(Long cid);

    @Query(nativeQuery = true, value = "SELECT\n" +
            "tb_course.cid,\n" +
            "tb_course.class1,\n" +
            "tb_course.credits,\n" +
            "tb_course.cname,\n" +
            "tb_course.cproperty,\n" +
            "tb_course.csemester\n" +
            "FROM\n" +
            "tb_course\n" +
            "WHERE \n" +
            "tb_course.cid not in(\n" +
            "SELECT cid1 FROM tb_sc WHERE sid = :id\n" +
            ")")
    List<Course> getCourse(@Param("id") Long id);

    @Query(nativeQuery = true, value = "SELECT\n" +
            "tb_course.cid,\n" +
            "tb_course.class1,\n" +
            "tb_course.credits,\n" +
            "tb_course.cname,\n" +
            "tb_course.cproperty,\n" +
            "tb_course.csemester\n" +
            "FROM\n" +
            "tb_course\n" +
            "WHERE \n" +
            "tb_course.cid in(\n" +
            "SELECT cid1 FROM tb_sc WHERE sid = '2019131113' and flag =0\n" +
            ")")
    List<Course> getThisSemesterCourse(@Param("id") Long id);
}
